
-- test Implicit Transaction execute prepare
drop table if exists user;
CREATE TABLE user
(
 id BIGINT(20) NOT NULL COMMENT '主键ID',
 age INT(11) NULL DEFAULT NULL COMMENT '年龄',
 PRIMARY KEY (id)
);
INSERT INTO user (id, age) VALUES
(1, 18),
(2, 20),
(3, 28),
(4, 21),
(5, 24);


set autocommit=0;

set @id=6;
set @age=6;
prepare s1 from 'insert into user values (?,?)';
execute s1 using @id,@age;
deallocate prepare s1;
commit;
set autocommit=1;
select * from user;

set autocommit=0;

set @id=6;
prepare s2 from 'delete from user where id=?';
execute s2 using @id;
deallocate prepare s2;
commit;
set autocommit=1;
select * from user;

set autocommit=0;

set @id=5;
set @age=100;
prepare s3 from 'update user set age=? where id=?';
execute s3 using @age, @id;
deallocate prepare s3;
commit;
set autocommit=1;
select * from user;

set autocommit=0;

set @id=3;
prepare s4 from 'select * from user where id>?';
execute s4 using @id;
deallocate prepare s4;
prepare s5 from 'select * from user where id<?';
execute s5 using @id;
deallocate prepare s5;
commit;
set autocommit=1;

set autocommit=0;
prepare s6 from 'create table test_user(a int)';
execute s6;
deallocate prepare s6;
commit;
set autocommit=1;
show tables like 'test_user';

set autocommit=0;
prepare s7 from 'drop table test_user';
execute s7;
deallocate prepare s7;
commit;
set autocommit=1;
show tables like 'test_user';

drop table user;



-- test explicit transaction execute prepare
drop table if exists user;
CREATE TABLE user
(
 id BIGINT(20) NOT NULL COMMENT '主键ID',
 age INT(11) NULL DEFAULT NULL COMMENT '年龄',
 PRIMARY KEY (id)
);
INSERT INTO user (id, age) VALUES
(1, 18),
(2, 20),
(3, 28),
(4, 21),
(5, 24);

set @id=6;
set @age=6;
begin;
prepare s1 from 'insert into user values (?,?)';
execute s1 using @id,@age;
deallocate prepare s1;
commit;
select * from user;

set @id=6;
begin;
prepare s2 from 'delete from user where id=?';
execute s2 using @id;
deallocate prepare s2;
commit;
select * from user;

set @id=5;
set @age=100;
begin;
prepare s3 from 'update user set age=? where id=?';
execute s3 using @age, @id;
deallocate prepare s3;
commit;
select * from user;

set @id=3;
begin;
prepare s4 from 'select * from user where id>?';
execute s4 using @id;
deallocate prepare s4;
prepare s5 from 'select * from user where id<?';
execute s5 using @id;
deallocate prepare s5;
commit;

begin;
prepare s6 from 'create table test_user(a int)';
execute s6;
deallocate prepare s6;
commit;
show tables like 'test_user';


begin;
prepare s7 from 'drop table test_user';
execute s7;
deallocate prepare s7;
commit;
show tables like 'test_user';

